/**
 * @PROJECT 
 * @DATE 2018年7月12日 下午7:45:39
 * @AUTHOR LUYU
 */
package com.kool.devops.domain;

import com.kool.core.exception.AppException;
import com.kool.core.util.DateUtils;
import com.kool.core.util.FileUtils;
import com.kool.devops.bean.vo.ColumnBean;
import com.kool.devops.bean.vo.TableBean;
import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;
import org.apache.log4j.Logger;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.StringWriter;
import java.util.ArrayList;
import java.util.List;

/**
 * @DESCRIBE 根据数据库设计文档生成建表sql
 * @AUTHOR LUYU
 * @DATE 2018年7月12日 下午7:45:39
 *
 */
public class ExcelToSql {
	private static Logger logger = Logger.getLogger(ExcelToSql.class);

	/**
	 * 
	 * @DESCRIBE 执行
	 * @DATE 2018年7月12日 下午8:11:14
	 *
	 * @param filePath
	 * @param templatePath
	 * @param toDir
	 * @throws Exception
	 */
	public void generate(String filePath, String templatePath, String toDir) throws AppException {
		ExcelReader er = new ExcelReader(filePath);
		List<List<List<String>>> listSheet = er.read(1, 65535);

		FileUtils.writeFile(toSql(listSheet,templatePath), toDir, "UTF-8", false);
	}

	public String generate(InputStream fis, String filename, String templatePath) throws AppException {
        ExcelReader er = new ExcelReader();
        List<List<List<String>>> listSheet = er.read(fis, filename, 1, 65535);

        return toSql(listSheet,templatePath);
	}

	private String toSql(List<List<List<String>>>listSheet,String templatePath) throws AppException {
        StringBuffer sbSql = new StringBuffer();
        for (List<List<String>> sheet : listSheet) {
            TableBean table = sheetValueToTableBean(sheet);
            String sql = tableBeanToSql(table, templatePath);
            sbSql.append(sql);
            sbSql.append("\r\n\r\n");
        }
        return sbSql.toString();
    }

	/**
	 * 
	 * @DESCRIBE 表结构数据转sql文件，并保存到目标文件夹
	 * @DATE 2017年8月25日 下午9:10:23
	 *
	 * @param tableBean
	 * @param templatePath
	 * @throws IOException
	 * @throws Exception
	 */
	private String tableBeanToSql(TableBean tableBean, String templatePath) throws AppException {
		Configuration cfg = new Configuration(Configuration.DEFAULT_INCOMPATIBLE_IMPROVEMENTS);
		cfg.setDefaultEncoding("UTF-8");
		String sql = null;
		try {
			cfg.setDirectoryForTemplateLoading(new File(templatePath));
			Template t = cfg.getTemplate("table.ftl");
			StringWriter writer = new StringWriter();
			t.process(tableBean, writer);
			sql = writer.toString();
		} catch (IOException e) {
			throw new AppException("freemarker转sql错误");
		} catch (TemplateException e) {
			throw new AppException("freemarker转sql错误");
		}
		return sql;
	}

	/**
	 * 
	 * @DESCRIBE excel一页内容转成表结构数据
	 * @DATE 2017年8月25日 下午9:11:17
	 *
	 * @param list
	 * @return
	 * @throws Exception
	 */
	private TableBean sheetValueToTableBean(List<List<String>> list) throws AppException {
		TableBean tableBean = new TableBean();
		String createTime = DateUtils.getCurrentTimeString(DateUtils.FORMAT_TIMESTAMP);
		String tableId = list.get(0).get(1);
		String tableName = list.get(0).get(2);
		tableBean.setCreateTime(createTime);
		tableBean.setTableId(tableId);
		tableBean.setTableName(tableName);

		ArrayList<ColumnBean> columns = new ArrayList<ColumnBean>();
		ArrayList<String> pks = new ArrayList<String>();
		for (int i = 2; i < list.size(); i++) {
			List<String> listCell = list.get(i);
			if (listCell.get(0) == null || listCell.get(0).equals("")) {
				continue;
			}
			String colId = listCell.get(0);
			String colName = listCell.get(1);
			String colType = listCell.get(2);
			String colIsNullable = listCell.get(3);
			String colIsKey = listCell.get(4);
			String colComment = listCell.get(5);

			ColumnBean column = new ColumnBean();
			column.setId(colId);
			column.setName(colName);
			column.setType(colType);
			column.setIsNullable(colIsNullable);
			column.setIsKey(colIsKey);
			column.setComment(colComment);
			column.setBinary(isString(colType));

			columns.add(column);

			if ("Y".equals(colIsKey)) {
				pks.add(colId);
			}
		}
		tableBean.setColumns(columns);
		tableBean.setPks(pks);

		return tableBean;
	}

	/**
	 * @DESCRIBE 是否字符
	 * @DATE 2018年7月16日 下午9:37:09
	 *
	 * @param colType
	 * @return
	 */
	private boolean isString(String colType) {
		if (colType.indexOf("char") >= 0 || colType.indexOf("CHAR") >= 0 || colType.indexOf("varchar") >= 0
				|| colType.indexOf("VARCHAR") >= 0|| colType.indexOf("text") >= 0|| colType.indexOf("TEXT") >= 0) {
			return true;
		}
		return false;
	}
}
